﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using ClassLibrary01;
using System.Data;
using System.Text;

namespace Server
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        Conexao conexao = Conexao.getInstacia();

        [WebMethod]
        public List<Medico> MedicoPorEspecialidade(int cod_esp)
        {
            List<Medico> lista = new List<Medico>();
            RelatorioDao r = new RelatorioDao();
            lista = r.MedicoPorEspecialidadeDao(cod_esp);
            return lista;
        }

        [WebMethod]
        public List<String> carregarCombo(string tab)
        {
            List<String> lista = new List<String>();

            SqlConnection conn = conexao.Conectar();
            string query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@tabela and COLUMN_NAME <> 'senha' and COLUMN_NAME <> 'cpf' and COLUMN_NAME <> 'data_nasc' and COLUMN_NAME NOT LIKE 'cod%'";

            SqlCommand cmd = new SqlCommand(query, conn);
            SqlParameter tabela = cmd.Parameters.Add("@tabela", SqlDbType.VarChar);
            tabela.Value = tab;
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                String s = dr.GetSqlValue(0).ToString();
                lista.Add(s);
            }
            return lista;

        }

        [WebMethod]
        public List<Relatorio> consultaSimples()
        {

            List<Relatorio> lista = new List<Relatorio>();
            RelatorioDao r = new RelatorioDao();
            lista = r.consultaSimplesDao();
            return lista;

        }



        [WebMethod]
        public List<String> carregarCombo2(string tab)
        {
            List<String> lista = new List<String>();

            SqlConnection conn = conexao.Conectar();
            string query = "SELECT count ";

            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                String s = dr.GetSqlValue(0).ToString();
                lista.Add(s);
            }
            return lista;

        }



        [WebMethod]
        public List<Relatorio> qtdConsulta(string criterio)
        {
            List<Relatorio> lista = new List<Relatorio>();
            RelatorioDao r = new RelatorioDao();
            lista = r.qtdConsultaDao(criterio);
            return lista;

        }

        [WebMethod]
        public List<Relatorio> qtdEspecialidade()
        {
            List<Relatorio> lista = new List<Relatorio>();
            RelatorioDao r = new RelatorioDao();
            lista = r.qtdEspecialidadeDao();
            return lista;

        }

        [WebMethod]
        public List<Avaliacao> avaliacao()
        {
            List<Avaliacao> lista = new List<Avaliacao>();
            RelatorioDao r = new RelatorioDao();
            lista = r.avaliacaoDao();
            return lista;

        }

        [WebMethod]
        public List<String> Consulta(string table, List<Consulta> lista)
        {
            System.Text.StringBuilder sbSQL = new StringBuilder(500);

            List<SqlParameter> cParameters = new List<SqlParameter>();
            List<String> listaString = new List<String>(); //acho burrice, mas não quis perder tempo. talvez dê pra receber o sqlparameter direto na lista de string, ou ter retorno tipo sqlparameter.

            sbSQL.Append("SELECT * FROM" + table + "WHERE 1 = 1 ");

            foreach (Consulta c in lista)
            {
                sbSQL.Append(" AND " + c.Coluna.ToString() + " = @Valor");

                if (c.VarcharValue == null)
                {
                    cParameters.Add(new SqlParameter("@Valor", c.IntValue));
                }
                else
                {
                    cParameters.Add(new SqlParameter("@Valor", c.VarcharValue));
                }
            }

            SqlCommand oCommand = new SqlCommand(sbSQL.ToString(), conexao.Conectar());

            if (cParameters.Count != 0)
            {
                oCommand.Parameters.AddRange(cParameters.ToArray());
            }

            foreach (SqlParameter sp in cParameters)
            {
                String s = sp.Value.ToString();
                listaString.Add(s);
            }

            return listaString;
        }

        [WebMethod]
        public int quantidade(string table)
        {
            int qtd = 0;
            RelatorioDao r = new RelatorioDao();
            qtd = r.quantidadeDao(table);
            return qtd;
        }

        [WebMethod]
        public void testarConexao()
        {
            SqlConnection conn = conexao.Conectar();

        }


        [WebMethod]
        public int quantidadeValor(string table, string col, string val)
        {
            SqlConnection conn = conexao.Conectar();
            int qtd = 0;
            List<Consulta> lista = new List<Consulta>();

            string query = "select count(*) from " + table + " where " + col + " = '" + val + "'";


            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                qtd = dr.GetInt32(0);
            }

            conexao.Desconectar(conn);
            return qtd;
        }

        [WebMethod]
        public int qtdConsultaData(string col, string val, string ano, string mes, string dia)
        {
            SqlConnection conn = conexao.Conectar();
            int qtd = 0;
            List<Consulta> lista = new List<Consulta>();

            String query = "select count(*) from agregadoConsulta where year(data)=" + ano;

            if (val != "")
            {
                query += " and " + col + " = '" + val + "'";
            }

            if (mes != "")
            {
                query += " and month(data)=" + mes;

                if (dia != "")
                {
                    query += " and day(data)=" + dia;
                }
            }
            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                qtd = dr.GetInt32(0);
            }

            conexao.Desconectar(conn);
            return qtd;
        }


        [WebMethod]
        public int qtdConsultaMes(string ano)
        {
            SqlConnection conn = conexao.Conectar();
            int qtd = 0;
            List<Consulta> lista = new List<Consulta>();

            String query = "select count(*) from agregadoConsulta where year(dt_cons)=" + ano;

            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                qtd = dr.GetInt32(0);
            }

            conexao.Desconectar(conn);
            return qtd;
        }

        [WebMethod]
        public List<String> retornaAno()
        {
            List<String> lista = new List<String>();
            RelatorioDao r = new RelatorioDao();
            lista = r.retornaAnoDao();
            return lista;

        }

        [WebMethod]
        public List<string> retornaMes(String ano)
        {
            List<String> lista = new List<String>();
            RelatorioDao r = new RelatorioDao();
            lista = r.retornaMesDao(ano);
            return lista;

        }

        [WebMethod]
        public List<string> retornaDia(string ano, string mes)
        {
            List<String> lista = new List<String>();
            RelatorioDao r = new RelatorioDao();
            lista = r.retornaDiaDao(ano,mes);
            return lista;

        }


        public List<Relatorio> filtro(string ano, string mes,string dia)
        {
            List<Relatorio> lista = new List<Relatorio>();
            Relatorio r;
            SqlConnection conn = conexao.Conectar();
            string query = "select count(1), status from consulta where year(dt_cons)=" + ano + " and month(dt_cons)=" + mes + " and day(dt_cons)=" + dia + "group by status";

            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataReader dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                r = new Relatorio();
                r.Quantitativo = dr.GetInt32(0);
                r.Descricao = dr.GetSqlValue(1).ToString();
                lista.Add(r);
            }
            return lista;

        }


    }
}